﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DelonixRegia.Classes
{
    public class AccountManagementDBManager
    {
        private const string connectionString = "Data Source=.; Initial Catalog=DelonixRegia; Integrated Security=True;";

        public static Account AuthenticateUser(string username, string password)
        {
            Account a = null;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT TOP 1 * FROM Account WHERE username=@user AND password=@pass";
                comm.Parameters.AddWithValue("@user", username);
                comm.Parameters.AddWithValue("@pass", password);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    a = new Account();
                    a.accountID = (int)dr["accountID"];
                    a.username = (string)dr["username"];
                    a.password = (string)dr["password"];
                    a.contact = (string)dr["contact"];
                    a.emailAddress = (string)dr["emailAddress"];
                    a.userType = (int)dr["userType"];
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return a;
        }

        public static bool createAccount(string username, string password, string contact, string email, int dutyType)
        {
            int rowCreated = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "INSERT INTO Account(username, password, contact, emailAddress, userType) VALUES (@user, @pass, @contact, @email, @type)";
                comm.Parameters.AddWithValue("@user", username);
                comm.Parameters.AddWithValue("@pass", password);
                comm.Parameters.AddWithValue("@contact", contact);
                comm.Parameters.AddWithValue("@email", email);
                comm.Parameters.AddWithValue("@type", dutyType);

                rowCreated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (rowCreated == 1);
        }

        public static bool checkDuplicate(string username, string email)
        {
            int noOfRows = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT COUNT(*) FROM Account WHERE username=@username OR emailAddress=@email";
                comm.Parameters.AddWithValue("@username", username);
                comm.Parameters.AddWithValue("@email", email);

                noOfRows = (int)comm.ExecuteScalar();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return (noOfRows > 0);
        }

    }
}